Qid Examcode Question Qimpath opt1 opt2 opt3 opt4 opt5 opt6 opt7 opt8 opt9 opt10 opt11 opt12 opt13 opt14 opt15 impath1 impath2 impath3 impath4 impath5 impath6 impath7 impath8 impath9 impath10 impath11 impath12 impath13 impath14 impath15 answers uanswer CR Edit Delete
222 1Z0-071 Examine this data in the EMPLOYEES table: 222.quesql.png SELECT dept_id, INSTR(last_name, 'A'), SUM(salary) FROM employees GROUP BY dept_id; SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY dept_id; SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id; SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY dept_id; 1 SC edit delete
221 1Z0-071 Examine this partial statement: 221.quesql.png Both the query and the subquery can select any number of rows. The query can select only zero rows or one row, but the subquery can select any number of rows. Both the query and the subquery can select only zero rows or one row. The query can select any number of rows, but the subquery can select only zero rows or one row. 2 SC edit delete
220 1Z0-071 Which two are true about scalar subquery expressions? (Choose two.) They can return at most one row. You can use them as a default value for a column. You cannot correlate them with a table in the parent statement. You must enclose them in parentheses. They can return two columns. 1, 4 MC edit delete
219 1Z0-071 Examine the data in the PRODUCTS table: 219-quesql.png 1 and 2 1 and 3 1, 2, and 3 2 and 3 2 SC edit delete
218 1Z0-071 Examine the description of the ORDER_ITEMS table: 218-quesql.png quantity, unit_price quantity * unit_price quantity total_paid product_id 2, 4 MC edit delete
217 1Z0-071 Examine the data in the EMPLOYEES table. 217-quesql.png SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees; SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees; SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees; SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL (monthly_commission_pct, 0)) AS annual_comp FROM employees; 1 SC edit delete
216 1Z0-071 Which two are true about savepoints? (Choose two.) After issuing a savepoints, you can roll back to the savepoint name within the current transaction. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error. They make uncommitted updates visible to other sessions owned by the same user. After issuing a savepoint, you cannot roll back the complete transaction. You can commit updates done between two savepoints without committing other updates in the current transaction. They make uncommitted updates visible to sessions owned by other users. 1, 5 MC edit delete
215 1Z0-071 Examine the description of the EMPLOYEES table: 215-quesql.png Line 5 Line 8 Line 7 Line 3 4 SC edit delete
214 1Z0-071 Examine the description of the PROMOTIONS table: 214-quesql.png SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1; SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2; SELECT DISTINCT promo_cost ||' in '|| DISTINCT promo_category FROM promotions ORDER BY 1; SELECT promo_cost, promo_category FROM promotions ORDER BY 1; 2, 3 MC edit delete
213 1Z0-071 The ORDERS table has a column ORDER_DATE of data type DATE. The default display format for a date is DD-MON-RR. Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.) WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019' WHERE order_date > TO_DATE('JUL 10 2018', 'MON DD YYYY') WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6), 'MON DD YYYY') WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE,6), 'MON DD YYYY') WHERE order_date IN (TO_DATE('OCT 21 2018', 'Mon DD YYYY'), TO_CHAR('Nov 21 2018', 'Mon DD YYYY')) 1, 2 MC edit delete
212 1Z0-071 You have been tasked to create a table for a banking application. 212-quesql.png TIMESTAMP WITH LOCAL TIMEZONE TIMESTAMP WITH TIMEZONE INTERVAL DAY TO SECOND TIMESTAMP INTERVAL YEAR TO MONTH 3 SC edit delete
211 1Z0-071 Which two statements are true regarding a SAVEPOINT? (Choose two.) A SAVEPOINT does not issue a COMMIT Only one SAVEPOINT may be issued in a transaction Rolling back to a SAVEPOINT can undo a TRUNCATE statement Rolling back to a SAVEPOINT can undo a CREATE INDEX statement Rolling back to a SAVEPOINT can undo a DELETE statement 1, 5 MC edit delete
210 1Z0-071 Which three statements are true about single-row functions? (Choose three.) They can be nested to any level The data type returned can be different from the data type of the argument They can accept only one argument The argument can be a column name, variable, literal or an expression They can be used only in the WHERE clause of a SELECT statement They return a single result row per table 2, 4 MC edit delete
209 1Z0-071 Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.) Read data from an external table and load it into a table in the database Create a directory object for an external table Execute DML statements on an external table Query data from an external table Read data from a table in the database and insert it into an external table Create a directory object for a flat file 2, 4, 5 MC edit delete
208 1Z0-071 You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL; SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL; SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL; SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL; SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL; 1 SC edit delete
207 1Z0-071 Which three are true about the CREATE TABLE command? (Choose three.) It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint It implicitly executes a commit A user must have the CREATE ANY TABLE privilege to create tables It implicitly rolls back any pending transactions The owner of the table should have space quota available on the tablespace where the table is defined The owner of the table must have the UNLIMITED TABLESPACE system privilege 2, 3, 6 MC edit delete
206 1Z0-071 Which three statements are true regarding indexes? (Choose three.) A SELECT statement can access one or more indices without accessing any tables An update to a table can result in no updates to any of the table’s indexes A table belonging to one user can have an index that belongs to a different user A UNIQUE index can be altered to be non-unique An update to a table can result in updates to any or all of the table’s indexes When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped 1, 2, 6 MC edit delete
205 1Z0-071 The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. 205-quesql.png CONCAT(qty_sold, invoice_date) : requires explicit conversion invoice_date > ’01-02-2019’ : uses implicit conversion invoice_date = ’15-march-2019’ : uses implicit conversion qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion qty_sold = ‘0554982’ : requires explicit conversion 3, 4 MC edit delete
204 1Z0-071 An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transactions complete thereby committing the updates? (Choose three.) when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT when a CREATE INDEX statement is executed successfully in the same session when a COMMIT statement is issued by the same user from another session in the same database instance when the session logs out successfully when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session 2, 4, 5 MC edit delete
203 1Z0-071 Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.) Insert statements can always be done on a table through a view. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. Delete statements can always be done on a table through a view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. 1, 3, 4 MC edit delete
202 1Z0-071 The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second? SELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘O’) FROM customers; SELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘O’)) FROM customers; SELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘O’) FROM customers; SELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘O’) FROM customers; 4 SC edit delete
201 1Z0-071 Examine the description of the PRODUCTS table: 201-quesql.png SELECT product_id, (expiry_date - delivery_date) * 2 FROM products; SELECT product_id, unit_price | | 5 “Discount”, unit_price + surcharge – discount FROM products; SELECT product_id, unit_price, 5 “Discount”, unit_price + surcharge – discount FROM products; SELECT product_id, unit_price, unit_price + surcharge FROM products; SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; SELECT product_id, expiry_date * 2 FROM products; 2, 4, 6 MC edit delete
200 1Z0-071 Which three statements are true about inner and outer joins? (Choose three.) A full outer join must use Oracle syntax An inner join returns matched rows A left or right outer join returns only unmatched rows A full outer join returns matched and unmatched rows Outer joins can only be used between two per query Outer joins can be used when there are multiple join conditions on two tables 2, 3, 4 MC edit delete
199 1Z0-071 Which two statements are true about substitution variables? (Choose two.) A substitution variable can be used with any clause in a SELECT statement A substitution variable used to prompt for a column name must be enclosed in a single quotation marks A substitution variable prefixed with & always prompts only once for a value in a session A substitution variable can be used only in a SELECT statement A substitution variable used to prompt for a column name must be enclosed in double quotation marks A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session 2, 3 MC edit delete
198 1Z0-071 Which three are true about system and object privileges? (Choose three.) WITH GRANT OPTION can be used when granting an object privilege to both users and roles Adding a primary key constraint to an existing table in another schema requires a system privilege Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC 1, 3, 5 MC edit delete
197 1Z0-071 Which two statements are true about the rules of precedence for operators? (Choose two.) The concatenation operator | | is always evaluated before addition and subtraction in an expression Multiple parentheses can be used to override the default precedence of operators in an expression Arithmetic operators with equal precedence area evaluated from left to right within an expression NULLS influence the precedence of operators in an expression The + binary operator has the highest precedence in an expression in a SQL statement 2, 5 MC edit delete
196 1Z0-071 Which two statements are true about the SET VERIFY ON command? (Choose two.) It can be used in SQL Developer and SQL*Plus It displays values for variables used only in the WHERE clause of a query It can be used only in SQL*Plus It displays values for variables prefixed with && It displays values for variables created by the DEFINE command 3, 5 MC edit delete
195 1Z0-071 Which three statements are true about sequences in a single instance Oracle database? (Choose three.) A sequence can issue duplicate values A sequence’s unallocated cached value are lost if the instance shuts down Sequences can always have gaps Two or more tables cannot have keys generated from the same sequence A sequence can only be dropped by a DBA A sequence number that was allocated can be rolled back if a transaction fails 2, 5, 6 MC edit delete
194 1Z0-071 Which two statements are true about single-row functions? (Choose two.) CEIL: can be used for positive and negative numbers FLOOR: returns the smallest integer greater than or equal to a specified number TRUNC: can be used with NUMBER and DATE values CONCAT: can be used to combine any number of values MOD: returns the quotient of a division operation 3, 5 MC edit delete
193 1Z0-071 Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.) The output is sorted by the UNION ALL operator The names of columns selected in each SELECT statement must be identical The number of columns selected in each SELECT statement must be identical Duplicates are eliminated automatically by the UNION ALL operator NULLS are not ignored during duplicate checking 3, 5 MC edit delete
192 1Z0-071 You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session. Which query can be used? SELECT &&col1, &&col2 FROM &table WHERE &&condition = &&cond; SELECT &col1, &col2 FROM &&table WHERE &condition; SELECT &col1, &col2 FROM "&table" WHERE &condition; SELECT &&col1, &&col2 FROM &table WHERE &&condition; SELECT '&&col1', '&&col2' FROM &table WHERE '&&condition' = '&cond'; 4 SC edit delete
191 1Z0-071 Examine this description of the PRODUCTS table: 191-quesql.png DROP TABLE products; ALTER TABLE products DROP COLUMN expiry_date; ALTER TABLE products SET UNUSED (expiry_date); ALTER TABLE products DROP UNUSED COLUMNS; CREATE INDEX price_idx ON products (price); TRUNCATE TABLE products; 1, 5, 6 MC edit delete
190 1Z0-071 Which three statements are true about indexes and their administration in an Oracle database? (Choose three.) An index can be created as part of a CREATE TABLE statement. A DROP INDEX statement always prevents updates to the table during the drop operation. A unique and non-unique index can be created on the same table column. A descending index is a type of function-based index. If a query filters on an indexed column then it will always be used during execution of the query. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table. 1, 4, 6 MC edit delete
189 1Z0-071 Examine this partial command: 189-quesql.png the LOCATION clause the access driver TYPE clause the REJECT LIMIT clause the DEFAULT DIRECTORY clause the ACCESS PARAMETERS clause 1, 2 MC edit delete
188 1Z0-071 Which two are true about the WITH GRANT OPTION clause? (Choose two.) The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option. It can be used when granting privileges to roles. It cannot be used to pass on privileges to PUBLIC by the grantee. It can be used for system and object privileges. It can be used to pass on privileges to other users by the grantee. The grantee can grant the object privilege to any user in the database, with or without including this option. 4, 5 MC edit delete
187 1Z0-071 Which two statements are true about the DUAL table? (Choose two.) It can display multiple rows but only a single column. It can be accessed by any user who has the SELECT privilege in any schema. It can display multiple rows and columns. It consists of a single row and single column of VARCHAR2 data type. It can be used to display only constants or pseudo columns. It can be accessed only by the SYS user. 2, 4 MC edit delete
186 1Z0-071 Which two statements are true about INTERVAL data types? (Choose two.) The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value. INTERVAL DAY TO SECOND columns support fractions of seconds. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year. INTERVAL YEAR TO MONTH columns support yearly intervals. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column. 2, 5 MC edit delete
185 1Z0-071 Evaluate these commands which execute successfully: 185-quesql.png Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers. 2, 5 MC edit delete
184 1Z0-071 Which three statements are true about Structured Query Language (SQL)? (Choose three.) It best supports relational databases. It is used to define encapsulation and polymorphism for a relational table. It is the only language that can be used for both relational and object-oriented databases. It guarantees atomicity, consistency, isolation, and durability (ACID) features. It provides independence for logical data structures being manipulated from the underlying physical data storage. It requires that data be contained in hierarchical data storage. 4, 5, 6 MC edit delete
183 1Z0-071 You execute this command: 183-quesql.png It drops any triggers defined on the table. It always retains the space used by the removed rows. A ROLLBACK statement can be used to retrieve the deleted data. It retains the integrity constraints defined on the table. It retains the indexes defined on the table. A FLASHBACK TABLE statement can be used to retrieve the deleted data. 4, 5 MC edit delete
182 1Z0-071 Which three statements are true about defining relations between tables in a relational database? (Choose three.) Primary key columns allow null values. Every primary or unique key value must refer to a matching foreign key value. Foreign key columns allow null values. Every foreign key value must refer to a matching primary or unique key value. Unique key columns allow null values. 3, 4, 5 MC edit delete
181 1Z0-071 Which three are true about privileges and roles? (Choose three.) A role is owned by the user who created it. A role can contain a combination of several privileges and roles. System privileges always set privileges for an entire database. A user has all object privileges for every object in their schema by default. All roles are owned by the SYS schema. PUBLIC can be revoked from a user. PUBLIC acts as a default role granted to every user in a database. 2, 4, 7 MC edit delete
180 1Z0-071 Examine the description of the BOOKS table: 180-quesql.png The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed. The second ROLLBACK command replays the delete. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row. The second ROLLBACK command undoes the insert. The second ROLLBACK command does nothing. 3, 4 MC edit delete
179 1Z0-071 Which three statements are true about Data Manipulation Language (DML)? (Choose three.) UPDATE statements can have different subqueries to specify the values for each updated column. INSERT statements can insert NULLS explicitly into a column. DELETE statements can remove multiple rows based on multiple conditions. DML statements require a primary key be defined on a table. INSERT INTO…SELECT…FROM statements automatically commit. 1, 3, 5 MC edit delete
178 1Z0-071 Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.) Each SELECT statement in the compound query must have its own ORDER BY clause. Each SELECT statement in the compound query can have its own ORDER BY clause. Column positions must be used in the ORDER BY clause. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause. Only column names from the first SELECT statement in the compound query are recognized. 2, 5 MC edit delete
177 1Z0-071 Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.) Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Insert statements can always be done on a table through a view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions. 2, 3, 6 MC edit delete
176 1Z0-071 Examine the description of the EMPLOYEES table: 176-quesql.png SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id; SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id; SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date; SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id; 4 SC edit delete
175 1Z0-071 Which two statements are true about a self join? (Choose two.) It can be a left outer join. It must be a full outer join. It can be an inner join. It must be an equijoin. The join key column must have an index. 3, 5 MC edit delete
174 1Z0-071 Examine the description of the BOOKS_TRANSACTIONS table: 174-quesql.png WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’)); WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’, ‘A102’); WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = A101’ OR member_id = ‘A102’)); WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’); WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’); 1, 2 MC edit delete
173 1Z0-071 The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER (8, 2). 173-quesql.png A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #######. A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023. 3, 5 MC edit delete
172 1Z0-071 Examine this query: 172-quesql.png 8 rows 1 row 6 rows 3 rows 0 rows an error 2 SC edit delete
171 1Z0-071 Examine the description of the SALES1 table: 171-quesql.png SUBTRACT INTERSECT UNION ALL UNION MINUS 5 SC edit delete
170 1Z0-071 In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained a query? (Choose two.) PROMO_BEGIN_DATE – 5 will return a date. PROMO_BEGIN_DATE – SYSDATE will return a number. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date. PROMO_BEGIN_DATE – SYSDATE will return an error. 1, 2 MC edit delete
169 1Z0-071 You execute this query: 169-quesql.png It returns the date for the first Monday of the next month. It returns the date for the last Monday of the current month. It executes successfully but does not return any result. It generates an error. 1 SC edit delete
168 1Z0-071 Which statement is true regarding the default behavior of the ORDER BY clause? (Choose two.) In a character sort, the values are case-sensitive. NULLs are not included in the sort operation. Only columns that are specified in the SELECT list can be used in the ORDER BY clause. Numeric values are displayed in descending order if they have decimal positions. Column aliases can be used in the ORDER BY clause. 1, 5 MC edit delete
167 1Z0-071 Which two commands execute successfully? (Choose two.) 166-quesql.png GRANT CREATE SEQUENCE TO manager, emp; GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION; GRANT CREATE TABLE, emp TP manager; GRANT CREATE TABLE, SELECT ON hr.employees TO manager; 1, 4 MC edit delete
166 1Z0-071 Examine this statement: 166-quesql.png 0 rows an error 1 row 2 rows 3 SC edit delete
165 1Z0-071 Which two statements are true regarding the COUNT function? (Choose two.) A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any column. It can only be used for NUMBER data types. 2, 4 MC edit delete
164 1Z0-071 In which three situations does a new transaction always start? (Choose three.) when issuing a TRUNCATE statement after a SELECT statement was issued in the same session when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session when issuing a DML statement after a DML statement failed in the same session when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session 4, 5, 6 MC edit delete
163 1Z0-071 Examine this description of the PRODUCTS table: 163-quesql.png A B C D 163-Aqans.png 163-Bqans.png 163-Cqans.png 163-Dqans.png 2, 4 MC edit delete
162 1Z0-071 What is true about non-equijoin statement performance? he BETWEEN condition always performs less well than using the >= and <= conditions. The join syntax used makes no difference to performance. Table aliases can improve performance. The BETWEEN condition always performs better than using the >= and <= conditions. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. 3 SC edit delete
161 1Z0-071 Examine the description of the PRODUCT_STATUS table: 161-quesql.png SELECT prod_id "CURRNT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK'; SELECT prod_id || q'('s not available)' "CURRNT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK'; SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK'; SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK'; SELECT prod_id || q'('s not available)' 'CURRNT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK'; SELECT prod_id || q's not available" FROM product_status WHERE status = 'OUT OF STOCK'; 2, 5 MC edit delete
160 1Z0-071 Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.) The Oracle join syntax supports natural joins. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax. The Oracle join syntax supports creation of a Cartesian product of two tables. The SQL:1999 compliant ANSI join syntax supports natural joins. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. The Oracle join syntax only supports right outer joins. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables. 1, 4, 7 MC edit delete
159 1Z0-071 The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. 159-quesql.png SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees; SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees; SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees; SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees; 2 SC edit delete
158 1Z0-071 Which two are true about dropping columns from a table? (Choose two.) A column drop is implicitly committed. A column that is referenced by another column in any other table cannot be dropped. A column can be removed only if it contains no data. Multiple columns can be dropped simultaneously using the ALTER TABLE command. A column must be set as unused before it is dropped from a table. A primary key column cannot be dropped. 1, 6 MC edit delete
157 1Z0-071 Examine these statements executed in a single Oracle session: 157-quesql.png The code for pen is 1. There is no row containing pencil. The code for fountain pen is 3. The code for pen is 10. There is no row containing fountain pen. There is no row containing pen. 2, 3, 4 MC edit delete
156 1Z0-071 The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10; SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10; SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10; SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; 1, 3 MC edit delete
155 1Z0-071 Which CREATE TABLE statement is valid? A B C D 155-Aqans.png 155-Bqans.png 155-Cqans.png 155-Dqans.png 4 SC edit delete
154 1Z0-071 Examine the description of the CUSTOMERS table: 154-quesql.png LEFT OUTER JOIN with self join self join RIGHT OUTER JOIN with self join FULL OUTER JOIN with self join subquery 2, 5 MC edit delete
153 1Z0-071 Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.) A GLOBAL TEMPORARY TABLE can have multiple indexes. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM. A trigger can be created on a GLOBAL TEMPORARY TABLE. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view. A GLOBAL TEMPORARY TABLE can have only one index. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO. 3, 4, 6 MC edit delete
152 1Z0-071 Examine the description of the SALES table: 152-quesql.png SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table. SALES1 is created with 55,000 rows. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. SALES1 is created with no rows. SALES1 is created with 1 row. 1, 4 MC edit delete
151 1Z0-071 Which three actions can you perform only with system privileges? (Choose three.) Query any table in a database. Log in to a database. Access flat files via a database, which are stored in an operating system directory. Truncate a table in another schema. Execute a procedure in another schema. Use the WITH GRANT OPTION clause. 1, 2, 6 MC edit delete
150 1Z0-071 Examine the data in the CUST_NAME column of the CUSTOMERS table: 150-quesql.png WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1) LIKE ‘Mc%’ WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1)) IN (‘MC%’, ‘Mc%') WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1)) LIKE UPPER(‘MC%’) WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1) LIKE ‘Mc%’ OR ‘MC%’ WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1)) LIKE ‘Mc%’ 1, 5 MC edit delete
149 1Z0-071 Examine the description of the CUSTOMERS table: 149-quesql.png WHERE (INITCAP(cust_last_name) LIKE 'A%' OR INITCAP(cust_last_name) LIKE 'B%') AND cust_credit_limit < 1000; WHERE UPPER(cust_last_name) BETWEEN UPPER ('A%' AND 'B%') AND ROUND(cust_credit_limit) < 1000; WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000; WHERE (UPPER(cust_last_name) LIKE 'A%' OR UPPER(cust_last_name) LIKE 'B%') AND ROUND(cust_credit_limit) < 1000; WHERE (UPPER(cust_last_name) LIKE INITCAP ('A%') OR UPPER(cust_last_name) LIKE INITCAP ('B%')) AND ROUND(cust_credit_limit) < 1000; 1, 4 MC edit delete
148 1Z0-071 Which three statements are true about views in an Oracle Database? (Choose three.) Views can join tables only if they belong to the same schema. A view can be created that refers to a non-existent table in its defining query. Views have no object number. Views have no segment. Rows inserted into a table using a view are retained in the table if the view is dropped. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query. 2, 4, 5 MC edit delete
147 1Z0-071 Examine the description of the CUSTOMERS table: 147-quesql.png A B C D E 147-Aqans.png 147-Bqans.png 147-Cqans.png 147-Dqans.png 147-Eqans.png 1 SC edit delete
146 1Z0-071 The SQL statements executed in a user session are as follows: 146-quesql.png The rollback generates an error. No SQL statements are rolled back. Only the DELETE statements are rolled back. Only the second DELETE statement is rolled back. Both the DELETE statements and the UPDATE statement are rolled back. 1, 2 MC edit delete
145 1Z0-071 Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.) DELETE can be used to remove rows from only one table in one statement. DELETE can be used to remove rows from multiple tables in one statement. DELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent. DELETE can be used to remove data from specific columns as well as complete rows. DELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule. 1, 5 MC edit delete
144 1Z0-071 Examine the data in the ORD_ITEMS table: 144-quesql.png It returns an error because the HAVING clause should be specified after the GROUP BY clause. It returns an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table. 3 SC edit delete
143 1Z0-071 You need to display the first names of all customers from the CUSTOMERS table that contain the character ‘e’ and have the character ‘a’ in the second last position. Which query would give the required output? A B C D 143-Aqans.png 143-Bqans.png 143-Cqans.png 143-Dqans.png 1 SC edit delete
142 1Z0-071 View the exhibit and examine the description of SALES and PROMOTIONS tables. 142-quesql.png A B C D 142-Aqans.png 142-Bqans.png 142-Cqans.png 142-Dqans.png 2, 3, 4 MC edit delete
141 1Z0-071 Examine the structure of the DEPARTMENTS table. 141-quesql.png Synonyms existing of the DEPARTMENTS table would have to be re-created. Unique key constraints defined on the COUNTRY column are removed. Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command. 2, 5 MC edit delete
140 1Z0-071 You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE. 140-quesql.png A B C D 140-Aqans.png 140-Bqans.png 140-Cqans.png 140-Dqans.png 2 SC edit delete
139 1Z0-071 What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models? OODB supports multiple objects in the same database, whereas RDB supports only tables. RDB supports only E.F. Codd’s rules, whereas OODB does not support them. OODB incorporates methods with data structure definition, whereas RDB does not allow this. RDB allows the definition of relationships between different tables, whereas OODB does not allow this. 3 SC edit delete
138 1Z0-071 Evaluate the following SQL statement: 138-quesql.png It executes successfully and displays rows in the descending order of PROMO_CATEGORY. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement. 4 SC edit delete
137 1Z0-071 View the Exhibit and examine the structure of the CUSTOMERS table. 137-quesql.png The CUSTOMER_ID column cannot be updated. The INTO clause is misplaced in the command. The WHERE clause cannot be used with INSERT. CUSTOMER_VU cannot be used as a data source. 1 SC edit delete
136 1Z0-071 View the Exhibit and examine the description of the EMPLOYEES table. 136-quesql.png The query would execute to give the desired output. The query would not execute because date functions cannot be nested. The query would execute but the output would give review dates that are Sundays. The query would not execute because the NEXT_DAY function accepts a string as argument. 3 SC edit delete
135 1Z0-071 Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements? DELTE and TRUNCATE statements can have a rollback done to restore data into a table. DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed. DELETE but not TRUNCATE statement can be used to remove data from selective columns and rows of a table. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table. 4 SC edit delete
134 1Z0-071 The customers table has the following structure: 134-quesql.png A B C D 134-Aqans.png 134-Bqans.png 134-Cqans.png 134-Dqans.png 2 SC edit delete
133 1Z0-071 View the Exhibit and examine the data in the PRODUCTS table. 133-quesql.png ALTER TABLE products ADD price NUMBER(8,2) NOT NULL; ALTER TABLE products ADD price NUMBER(8,2) DEFAULT NOT NULL; ALTER TABLE products ADD price NUMBER(8,2) DEFAULT 0 NOT NULL; ALTER TABLE products ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL. 3 SC edit delete
132 1Z0-071 Examine the structure and data in the PRICE_LIST table: 132-quesql.png SELECT TO_CHAR (prod_price* .25, ‘$99,999.99’) FROM PRICE_LIST SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, ‘$99,999.00’) FROM PRICE_LIST SELECT TO_CHAR (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’) FROM PRICE_LIST SELECT TO_NUMBER (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’) FROM PRICE_LIST 3 SC edit delete
131 1Z0-071 Examine the structure of the SHIPMENTS table: 131-quesql.png Both execute successfully and give correct results. Only the first query executes successfully but gives a wrong result. Only the first query executes successfully and gives the correct result. Only the second query executes successfully but gives a wrong result. Only the second query executes successfully and gives the correct result. 3 SC edit delete
130 1Z0-071 Which three tasks can be performed by DDL statements? (Choose three.) providing an alternative name for a table modifying a table to prevent data that violate certain conditions from being entered in a column preventing any data modification to a table preventing data retrieval from a table outside of office hours creating multiple savepoints to enable partial rollback of a transaction 1, 2, 3 MC edit delete
129 1Z0-071 View the Exhibit and examine the data in EMP and DEPT tables. 129-quesql.png Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back. Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back. Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back. 4 SC edit delete
128 1Z0-071 View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. 128-quesql.png A B C D 128-Aqans.png 128-Bqans.png 128-Cqans.png 128-Dqans.png 1 SC edit delete
127 1Z0-071 View the Exhibit and examine the structure of the PRODUCT table. 127-quesql.png display all products whose minimum list price is more than the average list price of products having the status ‘orderable’ display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’ display the number of products whose list prices are more than the average list price display all suppliers whose list price is more than 1000 display the minimum list price for each product status 1, 3 MC edit delete
126 1Z0-071 View the Exhibit and examine the structure of the CUSTOMERS table. 126-quesql.png Only the second query gives the correct result Both execute successfully but do not give the required result Only the first query gives the correct result Both execute successfully and give the same result 3 SC edit delete
125 1Z0-071 The PRODUCTS table has the following structure. 125-quesql.png Both the statements execute and give different results Only the second SQL statement executes successfully Both the statements execute and give the same result Only the first SQL statement executes successfully 1 SC edit delete
124 1Z0-071 View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables. 124-quesql.png DELETE * FROM order_items WHERE order_id IN (SELECT order_id) FROM orders WHERE order_status IN (0,1)); DELETE FROM (SELECT * FROM order_items I,orders o WHERE i.order_id = o.order_id AND order_status IN (0,1)); DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i.order_id = o.order_id AND order_status IN (0,1)); DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE orders_status in (0,1)); 2, 4 MC edit delete
123 1Z0-071 View the Exhibit and examine the details of the ORDER_ITEMS table. 123-quesql.png Statement 2 would return multiple rows of output. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns. Statement 1 would not return give the same output. Both the statements would give the same output. Statement 1 would return only one row of output. 1, 2, 5 MC edit delete
122 1Z0-071 View the Exhibit and examine the structure of the CUSTOMERS table. 122-quesql.png It returns an error because the BETWEEN operator cannot be used in the HAVING clause. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. It executes successfully. 4 SC edit delete
121 1Z0-071 Examine the structure of the BOOKS_TRANSACTIONS table. 121-quesql.png MEMBER_ID = ' '; MEMBER_ID = NULL; MEMBER_ID IS NULL; MEMBER_ID = ""; 3 SC edit delete
120 1Z0-071 Which three statements indicate the end of a transaction? (Choose three.) after a CREATE statement is issued after a SAVEPOINT is issued after a SELECT statement is issued after a ROLLBACK is issued after a COMMIT is issued 1, 4, 5 MC edit delete
119 1Z0-071 Which statement is true regarding the SESSION_PRIVS dictionary view? It contains the object privileges granted to other users by the current user session. It contains the system privileges granted to other users by the current user session. It contains the current object privileges available in the user session. It contains the current system privileges available in the user session. 4 SC edit delete
118 1Z0-071 Evaluate the following CREATE SEQUENCE statement: 118-quesql.png 100 an error 10 1 4 SC edit delete
117 1Z0-071 Which two statements are true regarding views? (Choose two.) The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. Rows added through a view are deleted from the table automatically when the view is dropped. A simple view in which column aliases have been used cannot be updated. A subquery used in a complex view definition cannot contain group functions or joins. 2, 3 MC edit delete
116 1Z0-071 View the Exhibit and examine the data in the PROMOTIONS table. 116-quesql.png SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00'; SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00'; SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000'; SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00'; 4 SC edit delete
115 1Z0-071 In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years. 115-quesql.png It executes successfully but no rows updated. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days. It gives an error because multiple NVL functions are used in an expression. It gives an error because NVL function cannot be used with UPDATE. 2 SC edit delete
114 1Z0-071 Examine the data in the CUSTOMERS table: 114-quesql.png LEFT OUTER JOIN JOIN NATURAL JOIN RIGHT OUTER JOIN FULL OUTER JOIN 2, 4 MC edit delete
113 1Z0-071 Examine the structure of the MARKS table: 113-quesql.png SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2; SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1); SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL; SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE ‘R%’; 1, 3 MC edit delete
112 1Z0-071 Examine the structure proposed for the TRANSACTIONS table: 112-quesql.png The CUST_CREDIT_VALUE column would allow storage of positive and negative integers. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format. 1, 2 MC edit delete
111 1Z0-071 Which statement is true regarding the USING clause in table joins? (Choose two.) It can be used to join a maximum of three tables. It can be used to access data from tables through equijoins as well as nonequijoins. It can be used to join tables that have columns with the same name and compatible data types. It can be used to restrict the number of columns used in a NATURAL join. 3, 4 MC edit delete
110 1Z0-071 View the Exhibit and examine the description of the EMPLOYEES table. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total” FROM EMPLOYEES; SELECT first_name, salary, salary*12+salary*commission_pct “Total” FROM EMPLOYEES; SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total” FROM EMPLOYEES; SELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, “Total” FROM EMPLOYEES; 1 SC edit delete
109 1Z0-071 View the Exhibit and examine the data in the PRODUCT_INFORMATION table. 109-quesql.png displaying all the products whose minimum list prices are more than average list price of products having the status orderable displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE displaying the number of products whose list prices are more than the average list price displaying all supplier IDs whose average list price is more than 500 displaying the minimum list price for each product status 1, 3 MC edit delete
108 1Z0-071 SCOTT is a user in the database. 108-quesql.png Statement 1 would not execute because the WITH GRANT option is missing. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement. Statement 1 would not execute because the IDENTIFIED BY clause is missing. 2 SC edit delete
107 1Z0-071 View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 107-quesql.png It would not execute because the entire WHERE clause is not enclosed within parentheses. It would execute but would return no rows. It would not execute because the same column has been used twice with the AND logical operator. It would execute and return the desired result. 2 SC edit delete
106 1Z0-071 View the Exhibit and examine the data in the EMPLOYEES table. 106-quesql.png It executes successfully but does not give the correct output. It generates an error because the concatenation operator can be used to combine only two items. It generates an error because the usage of the ROUND function in the expression is not valid. It generates an error because the alias is not valid. It executes successfully and gives the correct output. 1 SC edit delete
105 1Z0-071 View the Exhibits and examine PRODUCTS and SALES tables. 105-quesql.png The statement executes successfully and produces the required output. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause. The statement produces an error because ITEM_CNT cannot be displayed in the outer query. 1 SC edit delete
104 1Z0-071 Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c? ALTER TABLE SPLIT PARTITION ALTER TABLE MERGE PARTITION ALTER TABLE TRUNCATE PARTITION ALTER TABLE ADD PARTITION ALTER TABLE DROP PARTITION ALTER TABLE MOVE PARTITION 3, 5 MC edit delete
103 1Z0-071 Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c? By default, EM express is available for a database after database creation. You can use EM express to manage multiple databases running on the same server. You can perform basic administrative tasks for pluggable databases by using the EM express interface. You cannot start up or shut down a database Instance by using EM express. You can create and configure pluggable databases by using EM express. 1 SC edit delete
102 1Z0-071 You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use? Compare Period ADDM report. AWR Compare Period report. Active Session History (ASH) report. Taking a new snapshot and comparing it with a preserved snapshot. 2 SC edit delete
101 1Z0-071 Which two statements are true regarding constraints? A foreign key column cannot contain null values. A column with the UNIQUE constraint can contain null values. A constraint is enforced only for INSERT operation on the table. A constraint can be disabled even if the constraint column contains data. All constraints can be defined at the column level and at the table level. 2, 4 MC edit delete
100 1Z0-071 View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. 100-quesql.png The statement would execute successfully to produce the required output. The statement would not execute because inline views and outer joins cannot be used together. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query. The statement would not execute because the GROUP BY clause cannot be used in the inline. 1 SC edit delete
99 1Z0-071 View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables. 99-quesql.png INTERSECT UNION ALL MINUS UNION 3 SC edit delete
98 1Z0-071 See the Exhibit and examine the structure of the PROMOTIONS table: 98-quesql.png It generates an error because multiple conditions cannot be specified for the WHEN clause. It executes successfully and gives the required result. It generates an error because CASE cannot be used with group functions. It generates an error because NULL cannot be specified as a return value. 2 SC edit delete
97 1Z0-071 View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. (Choose the best answer.) 97-quesql.png SELECT product_id, UPPER(product_name) FROM product_information WHERE UPPER(product_name) = 'LASERPRO' OR list_price > 1000; SELECT UPPER(product_name) FROM product_information; SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254; SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE'); 4 SC edit delete
96 1Z0-071 View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.) 96-quesql.png ORDER BY DEPT_NAME; ORDER BY DEPT_ID; ORDER BY 'b'; ORDER BY 3; 2, 4 MC edit delete
95 1Z0-071 You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name. Which statement achieves those objectives? SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond'; SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond; SELECT &col1, &col2 FROM &&table WHERE &condition = &cond; SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond 3 SC edit delete
94 1Z0-071 A subquery is called a single-row subquery when _______. There is only one subquery in the outer query and the inner query returns one or more values The inner query returns a single value to the outer query. The inner query uses an aggregating function and returns one or more values. The inner query returns one or more values and the outer query returns a single value. 2 SC edit delete
93 1Z0-071 Which two are the minimal requirements for a self-join? (Choose two.) Only equijoin conditions may be used in the query. Outer joins must not be used in the query. There must be a condition on which the self-join is performed. No other condition except the self-join may be specified. The table used for the self-join must have two different alias names in the query. 3, 5 MC edit delete
92 1Z0-071 Examine the structure of the CUSTOMERS table: 92-quesql.png Subquery Self-join Full outer-join with self-join Left outer-join with self-join Right outer-join with self-join 1, 2 MC edit delete
91 1Z0-071 You must create a table for a banking application. 91-quesql.png DATE NUMBER TIMESTAMP INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH 4 SC edit delete
90 1Z0-071 Examine the structure of the MEMBERS table: (Choose the best answer.) 90-quesql.png It displays all cities in descending order, within which the last names are further sorted in descending order. It fails because a column alias cannot be used in the ORDER BY clause. It fails because a column number and a column alias cannot be used together in the ORDER BY clause. It displays all cities in ascending order, within which the last names are further sorted in descending order. 4 SC edit delete
89 1Z0-071 Which two statements are true regarding working with dates? (Choose two.) The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century. The default internal storage of dates is in character format. The default internal storage of dates is in numeric format. 1, 4 MC edit delete
88 1Z0-071 Which two statements are true regarding constraints? (Choose two.) All constraints can be defined at the table or column level. A constraint can be disabled even if the constrained column contains data. A column with a UNIQUE constraint can contain a NULL value. A column with a FOREIGN KEY constraint can never contain a NULL value. Constraints are enforced only during INSERT operations. 2, 3 MC edit delete
87 1Z0-071 Examine the structure of the SALES table. 87-quesql.png It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table. It will not be created because of the invalid WHERE clause. It is created with no rows. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table. 2, 4 MC edit delete
86 1Z0-071 View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.) 86-quesql.png A B C D 86-Aqans.png 86-Bqans.png 86-Cqans.png 86-Dqans.png 4 SC edit delete
85 1Z0-071 Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.) You can use the ON clause to specify multiple conditions while joining tables. You can explicitly provide the join condition with a NATURAL JOIN. You can use the JOIN clause to join only two tables. You can use the USING clause to join tables on more than one column. 1, 4 MC edit delete
84 1Z0-071 Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) The nested query executes after the outer query returns the row. The nested query executes first and then the outer query executes. The outer query executes only once for the result returned by the inner query. Each row returned by the outer query is evaluated for the results returned by the inner query. 1, 4 MC edit delete
83 1Z0-071 Which two statements are true regarding subqueries? (Choose two.) A subquery can appear on either side of a comparison operator. Only two subqueries can be placed at one level. A subquery can retrieve zero or more rows. A subquery can be used only in SQL query statements. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement. 1, 3 MC edit delete
82 1Z0-071 Examine the structure of the EMPLOYEES table. 82-quesql.png A B C D 82-Aqans.png 82-Bqans.png 82-Cqans.png 82-Dqans.png 2, 4 MC edit delete
81 1Z0-071 Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.) It can be used only with the SELECT clause. The WITH clause can hold more than one query. If the query block name and the table name are the same, then the table name takes precedence. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block 1, 2, 4 MC edit delete
80 1Z0-071 Which two statements are true regarding single row functions? (Choose two.) MOD : returns the quotient of a division. TRUNC : can be used with NUMBER and DATE values. CONCAT : can be used to combine any number of values. SYSDATE : returns the database server current date and time. INSTR : can be used to find only the first occurrence of a character in a string. TRIM : can be used to remove all the occurrences of a character from a string. 2, 4 MC edit delete
79 1Z0-071 Which three statements are true regarding subqueries? (Choose three.) The ORDER BY Clause can be used in a subquery. A subquery can be used in the FROM clause of a SELECT statement. If a subquery returns NULL, the main query may still return rows. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery. 1, 2, 3 MC edit delete
78 1Z0-071 Which two statements best describe the benefits of using the WITH clause? (Choose two.) It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query. It enables sessions to store a query block permanently in memory and use it to create complex queries. It enables sessions to store the results of a query permanently. 1, 2 MC edit delete
77 1Z0-071 View the Exhibit and examine the structure of the ORDER_ITEMS table. 77-quesql.png A B C D 77-Aqans.png 77-Bqans.png 77-Cqans.png 13-Dqans.png 1 SC edit delete
76 1Z0-071 Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.) During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution. During execution, the Oracle server may read data from storage if the required data is not already in memory. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution. 2 SC edit delete
75 1Z0-071 View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 75-quesql.png A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123. 1, 3 MC edit delete
74 1Z0-071 Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.) Finding the lowest value Finding the quotient Raising to a power Subtraction Addition 1, 3, 5 MC edit delete
73 1Z0-071 View the Exhibit and examine the description of the ORDERS table. 73-quesql.png WHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') ) WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY') WHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003' WHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY') 3, 4 MC edit delete
72 1Z0-071 View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.) 72-quesql.png The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks. The statement will fail because a subquery cannot be used in a VALUES clause. The statement will execute and a new row will be inserted in the SALES table. The statement will fail because the VALUES clause is not required with the subquery. 3 SC edit delete
71 1Z0-071 View the Exhibit and examine the structure of the CUSTOMERS table. 71-quesql.png SELECT NVL(TO_CHAR(cust_credit_limit*.15),‘Not Available’) “NEW CREDIT” FROM customers; SELECT TO_CHAR(NVL(cust_credit_limit*.15,‘Not Available’)) “NEW CREDIT” FROM customers; SELECT NVL(cust_credit_limit*.15,‘Not Available’) “NEW CREDIT” FROM customers; SELECT NVL(cust_credit_limit,‘Not Available’)*.15 “NEW CREDIT” FROM customers; 1 SC edit delete
70 1Z0-071 Examine the structure of the PROGRAMS table: 70-quesql.png SELECT NVL(ADD_MONTHS(END_DATE,1)SYSDATE) FROM programs; SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM programs; SELECT NVL(MONTHS_BETWEEN(start_date,end_date),‘Ongoing’) FROM programs; SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start-date,end_date)),‘Ongoing’) FROM programs; 2, 4 MC edit delete
69 1Z0-071 Evaluate the following CREATE TABLE commands: 69-quesql.png SYSDATE cannot be used with the CHECK constraint. The BETWEEN clause cannot be used for the CHECK constraint. The CHECK constraint cannot be placed on columns having the DATE data type. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY. 1 SC edit delete
68 1Z0-071 You issue this command which succeeds: 68-quesql.png All existing views and synonyms that refer to the table are invalidated but retained. Any uncommitted transaction in the session is committed. Table data and the table structure are deleted. All the table’s indexes if any exist, are invalidated but retained. Table data is deleted but the table structure is retained. 1, 3, 4 SC edit delete
67 1Z0-071 Which two statements are true regarding the SQL GROUP BY clause? (Choose two.) You can use a column alias in the GROUP BY clause. Using the WHERE clause after the GROUP BY clause excludes rows after creating groups. The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause. Using the WHERE clause before the GROUP BY clause excludes rows before creating groups. If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause. 4, 5 MC edit delete
66 1Z0-071 You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result? SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’) FROM DUAL SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’) FROM DUAL SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’) FROM DUAL SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’)) FROM DUAL 1 SC edit delete
65 1Z0-071 Which three statements are true regarding the SQL WHERE and HAVING clauses? The HAVING clause conditions can have aggregating functions. The HAVING clause conditions can use aliases for the columns. The WHERE and HAVING clauses cannot be used together in a SQL statement. The WHERE clause is used to exclude rows before grouping data. The HAVING clause is used to exclude one or more aggregated results after grouping data. 1, 4, 5 MC edit delete
64 1Z0-071 Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result? SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers 2 SC edit delete
63 1Z0-071 Evaluate the following CREATE TABLE command: 63-quesql.png It would execute successfully and only ORD_ITM_IDX index would be created. It would give an error because the USING INDEX clause cannot be used on a composite primary. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command. 1 SC edit delete
62 1Z0-071 View the Exhibit and examine the structure of the ORDER_ITEMS table. 62-quesql.png Remove the GROUP BY clause from the subquery and place it in the main query Replace = with the >ANY operator Replace = with the >ALL operator Replace = with the IN operator 4 SC edit delete
61 1Z0-071 Which statement is true about Data Manipulation Language (DML)? DML automatically disables foreign ley constraints when modifying primary key values in the parent table. Each DML statement forms a transaction by default. A transaction can consist of one or more DML statements. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint. 3 SC edit delete
60 1Z0-071 View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables. 60-quesql.png A B C D 60-Aqans.png 60-Bqans.png 60-Cqans.png 60-Dqans.png 4 SC edit delete
59 1Z0-071 View the exhibits and examine the structures of the COSTS and PROMOTIONS tables. 59-quesql.png It displays prod IDs in the promo with the lowest cost. It displays prod IDs in the promos with the lowest cost in the same time interval. It displays prod IDs in the promos with the highest cost in the same time interval. It displays prod IDs in the promos which cost less than the highest cost in the same time interval. 4 SC edit delete
58 1Z0-071 Which three statements are true? (Choose three.) The data dictionary is created and maintained by the database administrator. The data dictionary views consist of joins of dictionary base tables and user-defined tables. The usernames of all the users including database administrators are stored in the data dictionary. The USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply. Both USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user. Views with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary. 3, 4, 6 MC edit delete
57 1Z0-071 Which two statements are true regarding the USING and ON clauses in table joins? Both USING and ON clauses can be used for equijoins and nonequijoins. A maximum of one pair of columns can be joined between two tables using the ON clause. The ON clause can be used to join tables on columns that have different names but compatible data types. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause. 3, 4 MC edit delete
56 1Z0-071 View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables. 56-quesql.png The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table. 1 SC edit delete
55 1Z0-071 These are the steps for a correlated subquery, listed in random order: 55-quesql.png 2, 1, 4, 3 4, 1, 2, 3 4, 2, 1, 3 2, 4, 1, 3 4 SC edit delete
54 1Z0-071 Which two tasks can be performed by using Oracle SQL statements? (Choose two.) changing the password for an existing database user connecting to a database instance querying data from tables in different databases starting up a database instance executing operating system (OS) commands in a session 1, 3 MC edit delete
53 1Z0-071 Evaluate the following SQL statement: 53-quesql.png Remove the single quotation marks enclosing the character literal string in the SELECT clause Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause Enclose the character literal string in the SELECT clause within double quotation marks Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause 4 SC edit delete
52 1Z0-071 Which statement is true about an inner join specified in a query’s WHERE clause? It only applies for equijoin conditions. It applies for equijoin and nonequijoin conditions. It requires column names to be the same in all tables being joined. It must have primary-key and foreign-key constraints defined on the join columns. 2 SC edit delete
51 1Z0-071 Evaluate the following statement. 51-quesql.png They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true. 1 SC edit delete
50 1Z0-071 Which statement is true regarding external tables? The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table. The data and metadata for an external table are stored outside the database. The default REJECT LIMIT for external tables is UNLIMITED. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 1 SC edit delete
49 1Z0-071 Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.) WHERE and HAVING clauses cannot be used together in a SQL statement. The HAVING clause conditions can have aggregate functions. The HAVING clause conditions can use aliases for the columns. The WHERE clause is used to exclude rows before the grouping of data. The HAVING clause is used to exclude one or more aggregated results after grouping data. 2, 4, 5 MC edit delete
48 1Z0-071 Examine the following query: 48-quesql.png It displays 5 percent of the products with the highest amount sold. It displays the first 5 percent of the rows from the SALES table. It displays 5 percent of the products with the lowest amount sold. It results in an error because the ORDER BY clause should be the last clause. 3 SC edit delete
47 1Z0-071 Which statement is true regarding the INTERSECT operator? The names of columns in all SELECT statements must be identical. It ignores NULL values. Reversing the order of the intersected tables alters the result. The number of columns and data types must be identical for all SELECT statements in the query. 4 SC edit delete
46 1Z0-071 Which statement is true about an inner join specified in the WHERE clause of a query? It must have primary-key and foreign-key constraints defined on the columns used in the join condition. It requires the column names to be the same in all tables used for the join conditions. It is applicable for equijoin and nonequijoin conditions. It is applicable for only equijoin conditions. 3 SC edit delete
45 1Z0-071 View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables. 45-quesql.png A B C D 45-Aqans.png 45-Bqans.png 45-Cqans.png 45-Dqans.png 3 SC edit delete
44 1Z0-071 Which statement is true about transactions? A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. Each Data Definition Language (DDL) statement executed forms a single transaction. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction. 2 SC edit delete
43 1Z0-071 View the exhibit and examine the structure of the CUSTOMERS table. 43-quesql.png finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney' listing of customers who do not have a credit limit and were born before 1980 finding the number of customers, in each city, who’s marital status is 'married'. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'. 1, 5 MC edit delete
42 1Z0-071 Which two statements are true about sequences created in a single instance database? (Choose two.) When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement. DELETE would remove a sequence from the database. The numbers generated by a sequence can be used only for one table. CURRVAL is used to refer to the last sequence number that has been generated. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted. 1, 4 MC edit delete
41 1Z0-071 View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables. 41-quesql.png The table prefix is missing for the column names in the SELECT clause. The NATURAL JOIN clause is missing the USING clause. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type. 4 SC edit delete
40 1Z0-071 Which statements are correct regarding indexes? (Choose all that apply.) A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. Indexes should be created on columns that are frequently referenced as part of any expression. When a table is dropped, the corresponding indexes are automatically dropped. For each DML operation performed, the corresponding indexes are automatically updated. 1, 3, 4 MC edit delete
39 1Z0-071 View the exhibit and examine the structure of ORDERS and CUSTOMERS tables. 39-quesql.png A B C D 39-Aqans.png 39-Bqans.png 39-Cqans.png 39-Dqans.png 3 SC edit delete
38 1Z0-071 Which statement correctly grants a system privilege? GRANT CREATE VIEW ON table1 TO user1; GRANT ALTER TABLE TO PUBLIC; GRANT CREATE TABLE TO user1, user2; GRANT CREATE SESSION TO ALL; 3 SC edit delete
37 1Z0-071 View the exhibit for the structure of the STUDENT and FACULTY tables. 37-quesql.png Only statement 2 executes successfully and gives the required result. Only statement 1 executes successfully and gives the required result. Both statements 1 and 2 execute successfully and give different results. Both statements 1 and 2 execute successfully and give the same required result. 2 SC edit delete
36 1Z0-071 Which two tasks can be performed by using Oracle SQL statements? changing the password for an existing database user connecting to a database instance querying data from tables across databases starting up a database instance executing operating system (OS) commands in a session 1, 3 MC edit delete
35 1Z0-071 Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS: 35-quesql.png A B C D 35-Aqans.png 35-Bqans.png 35-Cqans.png 35-Dqans.png 2 SC edit delete
34 1Z0-071 Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.) You can use column alias in the GROUP BY clause. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause. 4, 5 MC edit delete
33 1Z0-071 You want to display the date for the first Monday of the next month and issue the following command: 33-quesql.png In generates an error because rrrr should be replaced by rr in the format string. It executes successfully but does not return the correct result. It executes successfully and returns the correct result. In generates an error because TO_CHAR should be replaced with TO_DATE. In generates an error because fm and double quotation marks should not be used in the format string. 3 SC edit delete
32 1Z0-071 You are designing the structure of a table in which two columns have the specifications: 32-quesql.png The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type. The EXECUTION_DATETIME must be of TIMESTAMP data type. The EXECUTION_DATETIME must be of DATE data type. The COMPONENT_ID must be of ROWID data type. The COMPONENT_ID must be of VARCHAR2 data type. The COMPONENT_ID column must be of CHAR data type. 3, 6 MC edit delete
31 1Z0-071 The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items TO PUBLIC; What correction needs to be done to the above statement? PUBLIC should be replaced with specific usernames. ALL should be replaced with a list of specific privileges. WITH GRANT OPTION should be added to the statement. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables. 4 SC edit delete
30 1Z0-071 Which three tasks can be performed using SQL functions built into Oracle Database? displaying a date in a nondefault format finding the number of characters in an expression substituting a character string in a text expression with a specified string combining more than two columns or expressions into a single column in the output 1, 2, 3 MC edit delete
29 1Z0-071 View the exhibit and examine the description of the PRODUCT_INFORMATION table. 28-quesql.png A B c D 28-Aqans.png 28-Bqans.png 28-Cqans.png 28-Dqans.png 2 SC edit delete
28 1Z0-071 Which three statements are true about the ALTER TABLE....DROP COLUMN.... command? A column can be dropped only if it does not contain any data. A column can be dropped only if another column exists in the table. A dropped column can be rolled back. The column in a composite PRIMARY KEY with the CASCADE option can be dropped. A parent key column in the table cannot be dropped. 2, 4, 5 MC edit delete
27 1Z0-071 Examine the structure of the INVOICE table. 27-quesql.png SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice; SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice; SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice; SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available') FROM invoice; 1, 3 MC edit delete
26 1Z0-071 View the exhibit and examine the ORDERS table. 26-quesql.png ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id); ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id); ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL; 3 SC edit delete
25 1Z0-071 Examine the structure of the BOOKS_TRANSACTIONS table: 25-quesql.png SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS; SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS; SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS; SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS; 3 SC edit delete
24 1Z0-071 Which task can be performed by using a single Data Manipulation Language (DML) statement? adding a column constraint when inserting a row into a table adding a column with a default value when inserting a row into a table removing all data only from one single column on which a unique constraint is defined removing all data only from one single column on which a primary key constraint is defined 3 SC edit delete
23 1Z0-071 Examine the structure of the MEMBERS table. 23-quesql.png SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI'; SELECT last_name, city FROM members WHERE state LIKE 'M%'; SELECT last_name, city FROM members WHERE state IN ('MO', 'MI'); SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI'; 3 SC edit delete
22 1Z0-071 Which statement is true regarding the default behavior of the ORDER BY clause? In a character sort, the values are case-sensitive. NULL values are not considered at all by the sort operation. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause. Numeric values are displayed from the maximum to the minimum value if they have decimal positions. 1 SC edit delete
21 1Z0-071 Which three statements are true regarding subqueries? Multiple columns or expressions can be compared between the main query and subquery. Subqueries can contain ORDER BY but not the GROUP BY clause. Main query and subquery can get data from different tables. Subqueries can contain GROUP BY and ORDER BY clauses. Main query and subquery must get data from the same tables. Only one column or expression can be compared between the main query and subquery. 1, 3, 4 MC edit delete
20 1Z0-071 Which three statements are true regarding the data types? The minimum column width that can be specified for a VARCHAR2 data type column is one. Only one LONG column can be used per table. A TIMESTAMP data type column stores only time values with fractional seconds. The BLOB data type column is used to store binary data in an operating system file. The value for a CHAR data type column is blank-padded to the maximum defined column width. 1, 2, 5 MC edit delete
19 1Z0-071 Evaluate the following SQL statements that are issued in the given order: 19-quesql.png It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it. It would remain disabled and has to be enabled manually using the ALTER TABLE command. It would be automatically enabled and immediate. It would be automatically enabled and deferred. 2 SC edit delete
18 1Z0-071 Evaluate the following ALTER TABLE statement: ALTER TABLE orders SET UNUSED (order_date); Which statement is true? After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table. The DESCRIBE command would still display the ORDER_DATE column. 1 SC edit delete
17 1Z0-071 Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table. 17-quesql.png INTERSECT UNION PLUS MINUS SUBTRACT 4 SC edit delete
16 1Z0-071 Which normal form is a table in if it has no multi-valued attributes and no partial dependencies? second normal form first normal form third normal form fourth normal form 1 SC edit delete
15 1Z0-071 Examine the structure of the EMPLOYEES table. 15-quesql.png A B C D 15-Aqans.png 15-Bqans.png 15-Cqans.png 15-Dqans.png 2 SC edit delete
14 1Z0-071 Which three statements are true about multiple-row subqueries? They can contain a subquery within a subquery. They can return multiple columns as well as rows. They cannot contain a subquery within a subquery. They can return only one column but multiple rows. They can contain group functions and GROUP BY and HAVING clauses. They can contain group functions and the GROUP BY clause, but not the HAVING clause. 1, 2, 5 MC edit delete
13 1Z0-071 View the exhibit and examine the structure of the EMPLOYEES table. 13-quesql.png A B C D 13-Aqans.png 13-Bqans.png 13-Cqans.png 13-Dqans.png 2 SC edit delete
12 1Z0-071 Examine the command: 12-quesql.png When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column. 3 SC edit delete
11 1Z0-071 The BOOKS_TRANSACTIONS table exists in your database. SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution? The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column. Rows are displayed in the order that they are stored in the table only for the first three rows. Rows are displayed sorted in ascending order of the values in the third column in the table. 4 SC edit delete
10 1Z0-071 View the exhibit and examine the structure of the STORES table. 10-quesql.png A B C D 10-Aqans.png 10-Bqans.png 10-Cqans.png 10-Dqans.png 4 SC edit delete
9 1Z0-071 Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.) The outer query stops evaluating the result set of the inner query when the first value is found. It is used to test whether the values retrieved by the inner query exist in the result of the outer query. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed. 1, 3 MC edit delete
8 1Z0-071 View the Exhibit and examine the details of PRODUCT_INFORMATION table. 8-quesql.png It would not execute because the same column has been used in both sides of the AND logical operator to form the condition. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses. It would execute and the output would display the desired result. It would execute but the output would return no rows. 4 SC edit delete
7 1Z0-071 Examine the business rule: 7-quesql.png The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. 2, 5 MC edit delete
6 1Z0-071 Evaluate the following two queries: 6-quesql.png Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column. There would be no change in performance. Performance would degrade in query 2. Performance would improve in query 2. 2 SC edit delete
5 1Z0-071 View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. 5-quesql.png It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement. It generates an error because a subquery cannot have a join condition in a UPDATE statement. It executes successfully and gives the correct result. It executes successfully but does not give the correct result. 4 SC edit delete
4 1Z0-071 View the Exhibit and examine the structure of CUSTOMERS table. 4-quesql.png SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT" FROM customers; SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers; 1 SC edit delete
3 1Z0-071 View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables. ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option. Which DELETE statement would execute successfully? 3-quesql.png DELETE orders o, order_items I WHERE o.order_id = i.order_id; DELETE FROM orders WHERE (SELECT order_id FROM order_items); DELETE orders WHERE order_total < 1000; DELETE order_id FROM orders WHERE order_total < 1000; 2 SC edit delete
2 1Z0-071 You execute the following commands: 2-quesql.png none, because no input required both the substitution variables 'hiredate' and 'mgr_id'. only 'hiredate' only 'mgr_id' 4 SC edit delete
1 1Z0-071 You issue the following command to drop the PRODUCTS table: SQL > DROP TABLE products; Which three statements are true about the implication of this command? All data along with the table structure is deleted. A pending transaction in the session is committed. All indexes on the table remain but they are invalidated. All views and synonyms on the table remain but they are invalidated. All data in the table is deleted but the table structure remains. 1, 2, 4 MC edit delete

Copyright © Designed & Developed by Sakthi Db Technology 2018